package com.bootcamp.s3_1013.database._1016_jdbc;

import com.bootcamp.s3_1013.database.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @program: myClassCode
 * @description:
 * @author: Keyu Li
 * @create: 2021-10-16 14:20
 **/

public class Ex1 {
    public static void main(String[] args) throws SQLException {
        //获取statement对象
        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        //创建表，同时插入数据
//        createTables(statement);
        //查询
        queryTables(statement);
        //关闭资源
        JDBCUtils.closeSources(connection, statement, null);
    }

    public static void createTables(Statement s) throws SQLException {
        String createMentor = "create table postgraduate\n" +
                "(\n" +
                "    id        int primary key auto_increment,\n" +
                "    name      varchar(20),\n" +
                "    gender    enum ('female','male'),\n" +
                "    admission date,\n" +
                "    mentor_id int\n" +
                ") character set utf8";

        String createPost = "create table mentor\n" +
                "(\n" +
                "    id       int primary key auto_increment,\n" +
                "    name     varchar(20),\n" +
                "    level    varchar(20),\n" +
                "    research varchar(20)\n" +
                ") character set utf8";

        String insertPost = "insert into postgraduate\n" +
                "values (1, '李易', 'male', '2000-1-1', 1),\n" +
                "       (2, '李耳', 'female', '2000-1-1', 2),\n" +
                "       (3, '李飒', 'female', '2000-1-1', 2),\n" +
                "       (4, '李斯', 'male', '2000-1-1', 3),\n" +
                "       (5, '李武卫', 'male', '2000-1-1', 1),\n" +
                "       (6, '李柳玉', 'female', '2000-1-1', 3),\n" +
                "       (7, '李琦姆', 'male', '2000-1-1', 1),\n" +
                "       (8, '李坝茶', 'female', '2000-1-1', 3),\n" +
                "       (9, '李玖舞', 'female', '2000-1-1', 1),\n" +
                "       (10, '李师翼', 'male', '2000-1-1', 4)";
        String insertMentor = "insert into mentor\n" +
                "values (1, '王翼', '高级', '计算机'),\n" +
                "       (2, '望珥', '中级', '数学'),\n" +
                "       (3, '汪散', '高级', '金融'),\n" +
                "       (4, '王思琪', '初级', '计算机')";

        s.executeUpdate(createMentor);
        s.executeUpdate(createPost);
        s.executeUpdate(insertMentor);
        s.executeUpdate(insertPost);
    }

    public static void queryTables(Statement s) throws SQLException {
        String query;
        ResultSet rs;

        //1.请查出每个导师所带研究生的姓名。
        System.out.println("--------------------");
        query = "select m.name mentor, group_concat(p.name) student\n" +
                "from mentor m,\n" +
                "     postgraduate p\n" +
                "where p.mentor_id = m.id\n" +
                "group by m.id";
        rs = s.executeQuery(query);
        while (rs.next()) {
            String mentor = rs.getString("mentor");
            String student = rs.getString("student");
            System.out.println("mentor: " + mentor + ", student: " + student);
        }

        //2.清查出特定姓名的导师所带研究生的姓名。
        System.out.println("--------------------");
        query = "select name\n" +
                "from postgraduate\n" +
                "where mentor_id = (select id from mentor where mentor.name = '汪散');";
        rs = s.executeQuery(query);
        while (rs.next()) {
            String name = rs.getString("name");
            System.out.println("student name: " + name);
        }

        //3.请查出每个导师所带研究生的数量。
        System.out.println("--------------------");
        query = "select m.name mentor, count(*) stuNum\n" +
                "from mentor m,\n" +
                "     postgraduate p\n" +
                "where m.id = p.mentor_id\n" +
                "group by m.id";
        rs = s.executeQuery(query);
        while (rs.next()) {
            String mentor = rs.getString("mentor");
            int stuNum = rs.getInt("stuNum");
            System.out.println("mentor: " + mentor + ", stuNum: " + stuNum);
        }

        //4.请查出每个导师所带的男研究生的数量。
        System.out.println("--------------------");
        query = "select m.name mentor, count(*) maleNum\n" +
                "from mentor m,\n" +
                "     postgraduate p\n" +
                "where m.id = p.mentor_id\n" +
                "  and p.gender = 'male'\n" +
                "group by m.id";
        rs = s.executeQuery(query);
        while (rs.next()) {
            String mentor = rs.getString("mentor");
            int maleNum = rs.getInt("maleNum");
            System.out.println("mentor: " + mentor + ", male num: " + maleNum);
        }

        //5.请找出选择哪个研究方向的导师最多。
        System.out.println("--------------------");
        query = "select research\n" +
                "from mentor\n" +
                "group by research\n" +
                "order by count(*) desc\n" +
                "limit 1";
        rs = s.executeQuery(query);
        while (rs.next()) {
            String research = rs.getString("research");
            System.out.println("research: " + research);
        }

        //6.请找统计不同职称的导师的个数。
        System.out.println("--------------------");
        query = "select level, count(*) num\n" +
                "from mentor\n" +
                "group by level";
        rs = s.executeQuery(query);
        while (rs.next()) {
            String level = rs.getString("level");
            int num = rs.getInt("num");
            System.out.println("level: " + level + ", num: " + num);
        }

        //关闭资源
        rs.close();
    }


}
